﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using System.Windows;
using System.Globalization;

namespace CreaMotion.Connectors
{
    public class MysqlConnector : CreaMotion.Interfaces.IDBConnector
    {
        public Interfaces.Database Connect()
        {

            Interfaces.Database db = new Interfaces.Database();

            // Common Variables
            ISQLQuery s = null;
            String query = null;


            // Get Selected Database
            query = "Select SCHEMA();";
            s = RunQuery(query);
            String selectedSchema = s.List()[0].ToString();


            // Get Tables
            query = String.Format("SHOW TABLE STATUS FROM {0};",selectedSchema);
            s = RunQuery(query);
            s.AddEntity(typeof(NhibernateClassBuilder.MySQL_Tables));


            // Get Table Fields
            var tables = s.List<NhibernateClassBuilder.MySQL_Tables>();
            foreach (var table in tables)
            {
                Interfaces.Table newTable = new Interfaces.Table()
                {
                    TableName = table.Name,
                    Comment = table.Comment,
                    Collation = table.Collation
                };

                // Get Table Details
                query = String.Format(@"Select
	                        c.COLUMN_NAME,
	                        c.COLUMN_DEFAULT,
	                        c.IS_NULLABLE,
	                        c.DATA_TYPE,
	                        c.CHARACTER_MAXIMUM_LENGTH,
	                        c.COLUMN_KEY,
	                        c.COLUMN_COMMENT,
                            c.COLLATION_NAME 
                        From information_schema.`COLUMNS` c
                        Where
		                    c.TABLE_SCHEMA = '{0}'
                        and	c.TABLE_NAME = '{1}'
                        Order By c.ORDINAL_POSITION;",selectedSchema,table.Name);


                // Add Field To List
                ISQLQuery detail = RunQuery(query);

                detail.AddEntity(typeof(NhibernateClassBuilder.MySQL_Columns));

                var fields = detail.List<NhibernateClassBuilder.MySQL_Columns>();
                foreach (var field in fields)
                {

                    Interfaces.Field newField = new Interfaces.Field
                    {
                        FieldName = field.COLUMN_NAME,
                        Comment = field.COLUMN_COMMENT,
                        DefaultValue = field.COLUMN_DEFAULT,
                        FieldType_db = field.DATA_TYPE,
                        FieldType_net = StrToType(field.DATA_TYPE),
                        IsPrimaryKey = (field.COLUMN_KEY == "PRI"),
                        NotNull = (field.IS_NULLABLE == "NO"),
                        ValueLength = Convert.ToInt64(field.CHARACTER_MAXIMUM_LENGTH == null ? "0" : field.CHARACTER_MAXIMUM_LENGTH),
                        Collation = field.COLLATION_NAME
                    };

                    // Add Indexes To List
                    query = String.Format(@"Select
	                                            s.INDEX_NAME,
	                                            s.INDEX_TYPE
                                            From information_schema.STATISTICS s
                                            Where
		                                        s.TABLE_SCHEMA = '{0}'
                                            and s.TABLE_NAME = '{1}'
                                            and	s.COLUMN_NAME = '{2}';",selectedSchema,table.Name,field.COLUMN_NAME);

                    ISQLQuery indexes = RunQuery(query);
                    indexes.AddEntity(typeof(NhibernateClassBuilder.MySQL_Index));

                    foreach (var index in indexes.List<NhibernateClassBuilder.MySQL_Index>())
                    {
                        Interfaces.Index newIndex = new Interfaces.Index
                        {
                            IndexName = index.INDEX_NAME,
                            IndexType = index.INDEX_TYPE
                        };

                        newField.Indexes.Add(newIndex);
                    }


                    newTable.Fields.Add(newField);

                }



                db.Tables.Add(newTable);
            }

            // DB Information
            return db;
        }

        private ISQLQuery RunQuery(String query)
        {
            try
            {
                return ConnectorManager.CurrentNHibernateSession.CreateSQLQuery(query);
            }
            catch
            {
                return null;
            }
        }

        private Type StrToType(string typeName)
        {
            typeName = typeName.ToUpper(new CultureInfo("en-US"));

            string textTypes = "CHAR,VARCHAR,TINYTEXT,TEXT,BLOB,MEDIUMTEXT,MEDIUMBLOB,LONGTEXT,LONGBLOB,ENUM,SET";
            string intTypes = "TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,FLOAT,DOUBLE,DECIMAL";
            string dataTypes = "DATE,DATETIME,TIMESTAMP,TIME";
            string boolTypes = "BIT";

            if (textTypes.IndexOf(typeName) != -1)
                return typeof(string);

            if (intTypes.IndexOf(typeName) != -1)
                return typeof(int);

            if (dataTypes.IndexOf(typeName) != -1)
                return typeof(DateTime);

            if (boolTypes.IndexOf(typeName) != -1)
                return typeof(bool);

            return null;
        }

        #region AboutConnector Function

        public Interfaces.About AboutConnector()
        {
            Interfaces.About inf = new Interfaces.About();
            inf.Developer = "Anti Fiko Şeytan Team";
            inf.Description = "MySQL Connector";
            inf.SupportedDatabaseSystem = "MySQL";
            inf.Version = "1.0.0";

            return inf;
        }

        #endregion
    }
}
